package com.example.demo;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.servlet.ServletUtil;
import cn.hutool.http.HttpRequest;
import cn.hutool.http.HttpResponse;
import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;

import java.net.HttpCookie;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 类描述信息
 *
 * @author TongDaShuai
 * @since 2021/10/21
 */
public class MyUtil {

    /**
     * 请求地址的ip和端口
     */
    public static final String URL_PRE = "http://192.168.188.36:9123";

    /**
     * 查询sql导出到excel文件
     *
     * @param eachNum 每次查询数量
     * @param cookies 浏览器查询sql的请求头cookie的值
     * @param token 浏览器查询sql的请求头X-CSRFToken的值
     * @param exportPath 导出excel文件路径
     * @param sql 查询的sql
     * @param dbName 数据库名称,比如"HZGR"
     * @param instanceName 实例名称，比如"无忧"
     */
    public static void querySqlToExcel(int eachNum, String cookies, String token, String exportPath, String sql, String dbName, String instanceName) {
        // 查询标题
        JSONArray titleJson = queryRowTitles(cookies, token, sql, dbName, instanceName);

        // 查询数据（每次eachnum条）
        List<JSONArray> rows = new ArrayList<>();

        int i = 1;
        // 循环查询数据，直到没有数据可查
        while (true) {
            int startRow = eachNum * (i - 1) + 1;
            int endRow = eachNum * i;
            System.out.println(StrUtil.format("page:{},startRow:{},endRow:{}", i, startRow, endRow));
            // 发送post请求查询并解析数据
            List<JSONArray> rowsItem = queryRows(startRow, endRow, cookies, token, sql, dbName, instanceName);
            rows.addAll(rowsItem);
            if (rowsItem.size() <= 0) {
                break;
            }
            i++;
        }

        // 导出数据到excel文件
        ExcelWriter writer = ExcelUtil.getWriter(exportPath);
        for (int i1 = 0; i1 < titleJson.size(); i1++) {
            writer.setColumnWidth(i1, 25);
            // 标题头单元格格式
            CellStyle headCellStyle = writer.getHeadCellStyle();
            // 非标题头单元格格式
            CellStyle cellStyle = writer.getCellStyle();
            // 没有描边
            cellStyle.setBorderBottom(BorderStyle.NONE);
            cellStyle.setBorderTop(BorderStyle.NONE);
            cellStyle.setBorderLeft(BorderStyle.NONE);
            cellStyle.setBorderRight(BorderStyle.NONE);
            headCellStyle.setBorderBottom(BorderStyle.NONE);
            headCellStyle.setBorderTop(BorderStyle.NONE);
            headCellStyle.setBorderLeft(BorderStyle.NONE);
            headCellStyle.setBorderRight(BorderStyle.NONE);
        }
        writer.writeHeadRow(titleJson);
        writer.write(rows, false);
        writer.close();
    }

    /**
     * 查询sql对应数据行
     *
     * @param startRow 起始行
     * @param endRow 结束行
     * @param cookies
     * @param token
     * @param sql
     * @param dbName 数据库名称,比如"HZGR"
     * @param instanceName 实例名称，比如"无忧"
     * @return
     */
    public static List<JSONArray> queryRows(int startRow, int endRow, String cookies, String token, String sql,
                                            String dbName, String instanceName) {
        List<JSONArray> rows = new ArrayList<>();
        String pageSql = " select * from (\n" +
                "  select d.*,rownum as rowno from (\n" +
                sql +
                "  ) d where rownum<=" + endRow + "\n" +
                ") dd where dd.rowno >=" + startRow + "\n" +
                "\n";
        Map<String, Object> formMap = new HashMap<>();
        formMap.put("instance_name", instanceName);
        formMap.put("db_name", dbName);
        formMap.put("sql_content", pageSql);
        formMap.put("limit_num", "1000");
        Map<String, String> hearderMap = new HashMap<>();
        hearderMap.put("X-CSRFToken", token);
        hearderMap.put("Cookie", cookies);
        HttpResponse response = HttpRequest.post(URL_PRE + "/query/")
                .cookie(cookies)
                .form(formMap)
                .headerMap(hearderMap, true)
                .execute();
        System.out.println("response:" + response);
        String body = response.body();
        JSONArray jsonArray = (JSONArray) JSONUtil.parseObj(body).getByPath("data.rows");

        for (int i = 0; i < jsonArray.size(); i++) {
            JSONArray row = (JSONArray) jsonArray.get(i);
            rows.add(row);
        }
        return rows;
    }

    /**
     * 查询sql对应数据行
     *
     * @param pageNum 页码
     * @param pageSize 每页数量
     * @param cookies
     * @param token
     * @param sql
     * @param dbName 数据库名称,比如"HZGR"
     * @param instanceName 实例名称，比如"无忧"
     * @return
     */
    public static List<JSONArray> queryRowsByMysql(int pageNum, int pageSize, String cookies, String token, String sql,
                                            String dbName, String instanceName) {
        List<JSONArray> rows = new ArrayList<>();
        String pageSql = "select temp.* from (\n" +
                "    " + sql + "  limit " + (pageNum - 1) * pageSize + "," + pageSize + "\n" +
                ") temp";
        Map<String, Object> formMap = new HashMap<>();
        formMap.put("instance_name", instanceName);
        formMap.put("db_name", dbName);
        formMap.put("sql_content", pageSql);
        formMap.put("limit_num", "1000");
        Map<String, String> hearderMap = new HashMap<>();
        hearderMap.put("X-CSRFToken", token);
        hearderMap.put("Cookie", cookies);
        HttpResponse response = HttpRequest.post(URL_PRE + "/query/")
                .cookie(cookies)
                .form(formMap)
                .headerMap(hearderMap, true)
                .execute();
        System.out.println("response:" + response);
        String body = response.body();
        JSONArray jsonArray = (JSONArray) JSONUtil.parseObj(body).getByPath("data.rows");

        for (int i = 0; i < jsonArray.size(); i++) {
            JSONArray row = (JSONArray) jsonArray.get(i);
            rows.add(row);
        }
        return rows;
    }

    /**
     * 查询sql返回的标题
     *
     * @param cookies
     * @param token
     * @param sql
     * @param dbName 数据库名称,比如"HZGR"
     * @param instanceName 实例名称，比如"无忧"
     * @return
     */
    public static JSONArray queryRowTitles(String cookies, String token, String sql,
                                           String dbName, String instanceName) {
        String tempSql = " select d.*,rownum as rowno from (\n" +
                sql +
                "  ) d where rownum<=1\n";
        Map<String, Object> formMap = new HashMap<>();
        formMap.put("instance_name", instanceName);
        formMap.put("db_name", dbName);
        formMap.put("sql_content", tempSql);
        formMap.put("limit_num", "1000");
        Map<String, String> hearderMap = new HashMap<>();
        hearderMap.put("X-CSRFToken", token);
        hearderMap.put("Cookie", cookies);
        HttpResponse response = HttpRequest.post(URL_PRE + "/query/")
                .cookie(cookies)
                .form(formMap)
                .headerMap(hearderMap, true)
                .execute();
        System.out.println("response:" + response);
        String body = response.body();
        JSONArray columnNames = (JSONArray) JSONUtil.parseObj(body).getByPath("data.column_list");

        return columnNames;
    }

    /**
     * 查询sql返回的标题(mysql)
     *
     * @param cookies
     * @param token
     * @param sql
     * @param dbName 数据库名称,比如"HZGR"
     * @param instanceName 实例名称，比如"无忧"
     * @return
     */
    public static JSONArray queryRowTitlesByMysql(String cookies, String token, String sql,
                                           String dbName, String instanceName) {

        String tempSql = "select temp.* from (\n" +
                "    " + sql + "  limit 1\n" +
                ") temp";
        Map<String, Object> formMap = new HashMap<>();
        formMap.put("instance_name", instanceName);
        formMap.put("db_name", dbName);
        formMap.put("sql_content", tempSql);
        formMap.put("limit_num", "1000");
        Map<String, String> hearderMap = new HashMap<>();
        hearderMap.put("X-CSRFToken", token);
        hearderMap.put("Cookie", cookies);
        HttpResponse response = HttpRequest.post(URL_PRE + "/query/")
                .cookie(cookies)
                .form(formMap)
                .headerMap(hearderMap, true)
                .execute();
        System.out.println("response:" + response);
        String body = response.body();
        JSONArray columnNames = (JSONArray) JSONUtil.parseObj(body).getByPath("data.column_list");

        return columnNames;
    }


    public static void main(String[] args) {
        // 需要的pom依赖
//        <!-- hutool -->
//        <dependency>
//            <groupId>cn.hutool</groupId>
//            <artifactId>hutool-all</artifactId>
//            <version>5.7.8</version>
//        </dependency>
//
//        <dependency>
//            <groupId>org.apache.poi</groupId>
//            <artifactId>poi-ooxml</artifactId>
//            <version>5.0.0</version>
//        </dependency>
        // ---------------------测试sql数据导出---------
        // --------------------- 参数控制start ------------
        // 登录名
        String username = "wellcom_smz";
        // 密码
        String password = "SUWell202109+";
        // 导出的excel文件名称
        String exportPath = "C:/Users/Administrator/Desktop/导出数据.xlsx";
        // 数据库用户名称
        String dbName = "HZGR";
//        dbName = "DFO";
        // 数据库实例名称
        String instanceName = "湖州";
        instanceName = "无忧";
        // 执行的sql
        String sql = "select t.id,t.name as 名称,t.phone as 手机号,t.address as 地址,t.birthdate as 生日,t.LASTLOGINTIME as 上次登录时间 from gstudent t where  t.id < 30000 order by id";
        sql = "select tt1.path as 驾校编码,\n" +
                "\t tt1.studentcode as 学员编码,\n" +
                "\t tt1.ywlx as 车型,\n" +
                "\t tt1.coursetype as 课程类型1计时2从业,\n" +
                "\t tt1.stage as 科目,\n" +
                "\t sum(tt1.completetime) as 时长,\n" +
                "\t sysdate as 导出时间\n" +
                "from (select distinct t3.id,t2.path,\n" +
                "\t\t\t\t\t  t1.studentcode,\n" +
                "\t\t\t\t\t  t4.ywlx,\n" +
                "\t\t\t\t\t  decode(t3.stage, '7', '2', '8', '2', '9', '2', '1') as coursetype,\n" +
                "\t\t\t\t\t  decode(t3.stage,\n" +
                "\t\t\t\t\t\t\t '7',\n" +
                "\t\t\t\t\t\t\t '1',\n" +
                "\t\t\t\t\t\t\t '8',\n" +
                "\t\t\t\t\t\t\t '2',\n" +
                "\t\t\t\t\t\t\t '9',\n" +
                "\t\t\t\t\t\t\t '4',\n" +
                "\t\t\t\t\t\t\t t3.stage) as stage,\n" +
                "\t\t\t\t\t  t3.completetime / 60 as completetime\n" +
                "\t\tfrom GSTUDENTREGISTRY t1\n" +
                "\t   inner join gbusinessorg t2\n" +
                "\t\t  on t1.businessorgid = t2.id\n" +
                "\t   inner join gbusinesstype t4\n" +
                "\t\t  on t1.businesstypeid = t4.id\n" +
                "\t   inner join gstudydetail_4511  t3\n" +
                "\t\t  on t1.studentcode = t3.studentcode and to_char(t1.registrydate,'yyyy-mm-dd') = t3.applytime\n" +
                "\t   WHERE 1=1\n" +
                "\t   and t1.learnstatus = '4'  \n" +
                "\t   and t2.path  in (\n" +
                "\t\t\t    '7213322621977183',\n" +
                "\t\t\t\t'7053331854561653',\n" +
                "\t\t\t\t'3833416521660538',\n" +
                "\t\t\t\t'5141156392132943',\n" +
                "\t\t\t\t'5202517255998023',\n" +
                "\t\t\t\t'4494813924113744',\n" +
                "\t\t\t\t'5625534319667283',\n" +
                "\t\t\t\t'4546259347401621',\n" +
                "\t\t\t\t'8336629556645635',\n" +
                "\t\t\t\t'6312663971694430',\n" +
                "\t\t\t\t'0593151650911420',\n" +
                "\t\t\t\t'1156013562339988',\n" +
                "\t\t\t\t'0000451100001087',\n" +
                "\t\t\t\t'5047861545060116',\n" +
                "\t\t\t\t'2786633227291252',\n" +
                "\t\t\t\t'0862337380385800',\n" +
                "\t\t\t\t'6796079563556565',\n" +
                "\t\t\t\t'4213152582443351',\n" +
                "\t\t\t\t'7558514471690225',\n" +
                "\t\t\t\t'1263645666214358',\n" +
                "\t\t\t\t'3735655484636346')\n" +
                "\t\t and t3.uploadstatus = '0' \n" +
                "\t\t ) tt1\n" +
                "group by tt1.path, tt1.studentcode, tt1.ywlx, tt1.coursetype, tt1.stage\n" +
                "\n" +
                "order by tt1.path, tt1.studentcode, tt1.ywlx, tt1.coursetype, tt1.stage";
        sql = "select t.id,t.name as 名称,t.phone as 手机号,t.address as 地址,t.birthdate as 生日,t.LASTLOGINTIME as 上次登录时间 from gstudent t where  t.id < 30000 order by id";

        // 每次查询的数量，不超过1000（美招的账号被限制只能查1000条了，超过1000条返回1000条）
        int eachNum = 1000;
        // --------------------- 参数控制end ------------


        // 登录前访问页面，获取token
        HttpResponse response = HttpRequest.get(URL_PRE + "/login/").execute();
        System.out.println("登录前response:" + response);
        String token = response.getCookie("csrftoken").getValue();

        // 登录，获取新的cookie和token
        response = HttpRequest.post(URL_PRE + "/authenticate/")
                .form("username", username)
                .form("password", password)
                .header("X-CSRFToken", token,  true)
                .execute();
        String cookies = CollUtil.join(response.getCookies(), ";");
        token = response.getCookie("csrftoken").getValue();
        System.out.println("登录请求response:" + response);

        // 查询sql导出到excel文件
        MyUtil.querySqlToExcel(eachNum, cookies, token, exportPath, sql, dbName, instanceName);
    }

}
